﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;

namespace QuizzAdminData
{
    /// <summary>
    /// Verzameling van DB methods
    /// </summary>
    /// <author>Kenny Vaneetvelde</author>
    public static class SpelDB
    {
        /// <summary>
        /// Haal alle records uit de tabel
        /// </summary>
        /// <author>Kenny Vaneetvelde</author>
        public static List<Spel> GetSpellen()
        {
            List<Spel> spellen = new List<Spel>();

            SqlConnection conn = QuizAdminDB.GetConnection();

            string selectStatement = "SELECT * FROM Spel";

            SqlCommand selectCmd = new SqlCommand(selectStatement, conn);

            try
            {
                conn.Open();
                SqlDataReader reader = selectCmd.ExecuteReader();
                while (reader.Read())
                {
                    Spel spel = new Spel();

                    spel.Id = (int)reader["id"];
                    spel.DateString = ((DateTime)reader["date_created"]).ToShortDateString();
                    spel.Teams_Scores = GetTeamsScores(spel.Id);
                    spel.Teams = TeamDB.GetTeamFromSpelID(spel.Id);
                    spel.Vragen = VragenDB.GetVragenFromSpelID(spel.Id);
                    spellen.Add(spel);
                }

                reader.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }

            return spellen;
        }

        /// <author>Kenny Vaneetvelde</author>
        public static void InsertNewSpel()
        {
            SqlConnection conn = QuizAdminDB.GetConnection();

            string sqlIns = "INSERT INTO Spel (date_created) VALUES ('" + DateTime.Today.ToShortDateString() + "')";

            try
            {
                conn.Open();

                SqlCommand cmdIns = new SqlCommand(sqlIns, conn);
                cmdIns.ExecuteNonQuery();
                cmdIns.Dispose();
                cmdIns = null;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <author>Kenny Vaneetvelde</author>
        public static void AddTeam(int spelID, int teamID)
        {
            SqlConnection conn = QuizAdminDB.GetConnection();

            string sqlIns = "INSERT INTO Spel_Teams_Scores (spel_id, team_id) VALUES (@spel_id, @team_id)";

            try
            {
                conn.Open();

                SqlCommand cmdIns = new SqlCommand(sqlIns, conn);
                cmdIns.Parameters.AddWithValue("spel_id", spelID);
                cmdIns.Parameters.AddWithValue("team_id", teamID);
                cmdIns.ExecuteNonQuery();
                cmdIns.Dispose();
                cmdIns = null;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <returns>Hashtable met formaat hashtable[teamid]=score</returns>
        /// <author>Kenny Vaneetvelde</author>
        public static Hashtable GetTeamsScores(int spelID)
        {
            Hashtable ht = new Hashtable();

            SqlConnection conn = QuizAdminDB.GetConnection();

            string selectStatement = "SELECT * FROM Spel_Teams_Scores WHERE spel_id=@id";

            SqlCommand selectCmd = new SqlCommand(selectStatement, conn);

            try
            {
                conn.Open();
                selectCmd.Parameters.AddWithValue("id", spelID);
                SqlDataReader reader = selectCmd.ExecuteReader();
                while (reader.Read())
                {
                    ht[reader["team_id"]] = reader["score"];
                }

                reader.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }

            return ht;
        }

    }
}
